#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
修复Excel文件中的字段名问题
"""

import pandas as pd
from pathlib import Path
import shutil
from datetime import datetime

def fix_excel_field_names():
    """修复Excel文件中的字段名问题"""
    excel_file = Path('tests/data/test_cases.xlsx')
    
    if not excel_file.exists():
        print(f"Excel文件不存在: {excel_file}")
        return
    
    # 创建备份
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_file = excel_file.with_suffix(f'.backup_{timestamp}.xlsx')
    shutil.copy2(excel_file, backup_file)
    print(f"已创建备份文件: {backup_file}")
    
    try:
        # 读取所有工作表
        xl = pd.ExcelFile(excel_file)
        print(f"\n处理工作表: {xl.sheet_names}")
        
        # 逐个处理工作表
        all_sheets = {}
        
        for sheet_name in xl.sheet_names:
            print(f"\n处理工作表: {sheet_name}")
            df = pd.read_excel(excel_file, sheet_name=sheet_name)
            
            print(f"原始列名: {list(df.columns)}")
            
            # 修复字段名
            if 'expected_status' in df.columns:
                df = df.rename(columns={'expected_status': 'expected_status_code'})
                print("✓ 已将 'expected_status' 重命名为 'expected_status_code'")
            
            # 检查是否有其他需要修复的字段
            required_fields = ['test_case_id', 'test_name', 'api_method', 'api_path', 'expected_status_code']
            missing_fields = [field for field in required_fields if field not in df.columns]
            
            if missing_fields:
                print(f"⚠️  仍然缺少字段: {missing_fields}")
            else:
                print("✓ 所有必填字段都存在")
            
            print(f"修复后列名: {list(df.columns)}")
            
            # 检查expected_status_code字段的值
            if 'expected_status_code' in df.columns:
                null_count = df['expected_status_code'].isnull().sum()
                if null_count > 0:
                    print(f"⚠️  expected_status_code字段有 {null_count} 个空值")
                    # 为空值填入默认状态码
                    df['expected_status_code'] = df['expected_status_code'].fillna(200)
                    print("✓ 已为空值填入默认状态码 200")
                
                print(f"expected_status_code值: {df['expected_status_code'].tolist()}")
            
            all_sheets[sheet_name] = df
            print(f"✓ 工作表 '{sheet_name}' 已处理")
        
        # 写入所有工作表
        with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
            for sheet_name, df in all_sheets.items():
                df.to_excel(writer, sheet_name=sheet_name, index=False)
                print(f"✓ 工作表 '{sheet_name}' 已保存")
        
        print(f"\n✅ Excel文件修复完成: {excel_file}")
        print(f"备份文件: {backup_file}")
        
    except Exception as e:
        print(f"❌ 修复过程中出现错误: {e}")
        # 恢复备份
        if backup_file.exists():
            shutil.copy2(backup_file, excel_file)
            print(f"已从备份恢复: {backup_file} -> {excel_file}")
        raise

if __name__ == "__main__":
    fix_excel_field_names()